﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Data.Linq;
using System.Text;
using IronOne.SalesOne.BusinessObjects;
using System.Configuration;

namespace IronOne.SalesOne.DataObjects
{
    public class LinqBranch
    {
        public int pageSize = Convert.ToInt32(ConfigurationManager.AppSettings["GridCount"]);

        /// <summary>
        /// Suren Manawatta
        /// 2012-11-21
        /// Get the branch list array when given the intranet username
        /// </summary>
        /// <param name="intranetUsrname"></param>
        /// <returns></returns>
        private string[] GetBranchListForUser(string intranetUsrname)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["System.Data.SqlClient"].ConnectionString;
                string users = "";
                string brList = "";
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    SqlParameter myParam = new SqlParameter("@UsernameParam", SqlDbType.VarChar, 50) { Value = intranetUsrname };

                    string connIP = ConfigurationManager.AppSettings["CeylincoDBIP"];

                    SqlCommand myCommand =
                        new SqlCommand("select BRANCH from [" + connIP + "].dwh.dbo.web_user_group " +
                                       "where EMPID = @UsernameParam", conn);

                    //SqlCommand myCommand =
                    //    new SqlCommand("select BRANCH from web_usergroup " +
                    //                   "where EMPID = @UsernameParam", conn);

                    myCommand.Parameters.Add(myParam);
                    SqlDataReader myReader = myCommand.ExecuteReader();

                    while (myReader.Read())
                    {
                        brList += myReader["BRANCH"] + ".";
                    }
                }
                
                string[] arr = brList.Split('.');
                StringBuilder sBuilder = new StringBuilder();
                for (int i = 0; i < arr.Length; i++)
                {
                    char[] brCode = arr[i].ToCharArray();
                    if (brCode.Length == 4)
                    {
                        sBuilder.Append(brCode[0]);
                        sBuilder.Append(brCode[1]);
                        sBuilder.Append(brCode[3]);
                        arr[i] = sBuilder.ToString();
                        sBuilder.Length = 0;
                    }
                }
                return arr;
            }
            catch (Exception e)
            {
                StreamWriter file = new StreamWriter(AppDomain.CurrentDomain.BaseDirectory + "\\ZLog.txt", true);
                file.WriteLine(e.Message);
                file.WriteLine(e.InnerException);
                file.Close();
                return null;
            }
        }

        public List<Branch> GetBranches(string sortExpression)
        {            
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<BranchEntity> query = db.BranchEntities;

                if (sortExpression.Length > 0)
                {
                    string[] sort = sortExpression.Split(' ');
                    string sortColumn = sort[0];
                    string sortOrder = sort[1];

                    switch (sortColumn)
                    {
                        case "BranchId":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.BranchId);
                            else
                                query = query.OrderByDescending(c => c.BranchId);
                            break;

                        case "BranchName":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.BranchName);
                            else
                                query = query.OrderByDescending(c => c.BranchName);
                            break;
                    }
                }
                return query.Select(c => Mapper.ToBranchObject(c)).ToList();
            }
        }

        /// <summary>
        /// Suren Manawatta
        /// 2012-11-21
        /// </summary>
        /// <param name="sortExpression"></param>
        /// <param name="intranetUsrname"></param>
        /// <returns></returns>
        public List<Branch> GetBranchesV2(string sortExpression, string intranetUsrname)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                string[] beArr = GetBranchListForUser(intranetUsrname);
                StreamWriter file2 = new StreamWriter(AppDomain.CurrentDomain.BaseDirectory + "\\ZLog.txt", true);
                file2.WriteLine("sort exp: " + sortExpression + "intranetUsrname" + intranetUsrname + 
                    " got the branch list from datawarehouse. no of branches : " + beArr.Length +
                    "first branch code is " + beArr[0]);
                file2.Close();
                //IQueryable<BranchEntity> query = db.BranchEntities;

                IQueryable<BranchEntity> query = from p in db.BranchEntities
                            where (beArr.Contains(p.BranchCode))
                            select p;

                if (sortExpression.Length > 0)
                {
                    string[] sort = sortExpression.Split(' ');
                    string sortColumn = sort[0];
                    string sortOrder = sort[1];

                    switch (sortColumn)
                    {
                        case "BranchId":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.BranchId);
                            else
                                query = query.OrderByDescending(c => c.BranchId);
                            break;

                        case "BranchName":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.BranchName);
                            else
                                query = query.OrderByDescending(c => c.BranchName);
                            break;
                    }
                }
                StreamWriter file1 = new StreamWriter(AppDomain.CurrentDomain.BaseDirectory + "\\ZLog.txt", true);
                file1.WriteLine("final branch list count : " + query.Count() + " then : " + query.Select(c => Mapper.ToBranchObject(c)).ToList().Count);
                file1.Close();

                return query.Select(c => Mapper.ToBranchObject(c)).ToList();
            }
        }

        /// <summary>
        /// Suren Manawatta
        /// 2012-11-21
        /// </summary>
        /// <param name="fromDate"></param>
        /// <param name="toDate"></param>
        /// <param name="brCodes"></param>
        /// <returns></returns>
        public DataTable GetDataByCollectionV2(DateTime fromDate, DateTime toDate, int[] brCodes)
        {
            try
            {
                using (SODataDataContext db = DataContextFactory.CreateContext())
                {
                    //SELECT Collection.SOCode, Collection.CollectionType, Collection.ReceiptDate, Collection.ContractNo, 
                    //Collection.PaymentMethod, Collection.CollectionAmt, Collection.VerifiedBy, Collection.VerifiedDate, 
                    //@fDate AS fromDate, @tDate AS toDate, Branches.BranchName, Collection.ReceiptSeqNo
                    //FROM Collection INNER JOIN
                    //     Users ON Collection.SOCode = Users.SOCode INNER JOIN
                    //     UsersInStructure ON Users.UserId = UsersInStructure.UserId INNER JOIN
                    //     Units ON UsersInStructure.NodeId = Units.UnitId INNER JOIN
                    //     Branches ON Units.BranchId = Branches.BranchId
                    //WHERE (Collection.VerifiedDate >= @fDate) AND (Collection.VerifiedDate <= @tDate) AND 
                    //      (Units.BranchId = @brId) AND (UsersInStructure.UserTypeId = 4) AND 
                    //      (LOWER(Collection.CollectionStatus) = 'verified')

                    var query = from c in db.CollectionEntities
                                      join u in db.UserEntities on c.SOCode equals u.SOCode
                                      join ui in db.UsersInStructureEntities on u.UserId equals ui.UserId
                                      join ue in db.UnitEntities on ui.NodeId equals ue.UnitId
                                      join b in db.BranchEntities on ue.BranchId equals b.BranchId

                                where (c.VerifiedDate >= fromDate) && (c.VerifiedDate <= toDate) &&
                                      (brCodes.Contains(ue.BranchId)) && (ui.UserTypeId == 4) &&
                                      (c.CollectionStatus.ToLower().Equals("verified"))

                                select new {c.SOCode, c.CollectionType, c.ReceiptDate, c.ContractNo,
                                       c.PaymentMethod, c.CollectionAmt, c.VerifiedBy, c.VerifiedDate,
                                       fromDate, toDate, b.BranchName, c.ReceiptSeqNo};


                    DataTable dt = new DataTable();
                    dt.Columns.Add("SOCode", typeof(string));
                    dt.Columns.Add("CollectionType", typeof(string));
                    dt.Columns.Add("ReceiptDate", typeof(DateTime));
                    dt.Columns.Add("ContractNo", typeof(string));
                    dt.Columns.Add("PaymentMethod", typeof(string));
                    dt.Columns.Add("CollectionAmt", typeof(double));
                    dt.Columns.Add("VerifiedBy", typeof(string));
                    dt.Columns.Add("VerifiedDate", typeof(DateTime));
                    dt.Columns.Add("fromDate", typeof(DateTime));
                    dt.Columns.Add("toDate", typeof(DateTime));
                    dt.Columns.Add("BranchName", typeof(string));
                    dt.Columns.Add("ReceiptSeqNo", typeof(Int64));

                    foreach (var q in query)
                    {
                        DataRow row = dt.NewRow();
                        row["SOCode"] = q.SOCode;
                        row["CollectionType"] = q.CollectionType;
                        row["ReceiptDate"] = q.ReceiptDate;
                        row["ContractNo"] = q.ContractNo;
                        row["PaymentMethod"] = q.PaymentMethod;
                        row["CollectionAmt"] = q.CollectionAmt;
                        row["VerifiedBy"] = q.VerifiedBy;
                        row["VerifiedDate"] = q.VerifiedDate;
                        row["fromDate"] = q.fromDate;
                        row["toDate"] = q.toDate;
                        row["BranchName"] = q.BranchName;
                        row["ReceiptSeqNo"] = q.ReceiptSeqNo;
                        dt.Rows.Add(row);
                    }
                    return dt;
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        public List<Branch> GetBranchesForGroupV2(int groupId, string userId)
        {
            try
            {
                string[] beArr = GetBranchListForUser(userId);

                using (SODataDataContext db = DataContextFactory.CreateContext())
                {
                    IQueryable<BranchEntity> query = db.BranchEntities;

                    query = from p in db.BranchEntities
                            where p.GroupId == groupId && (beArr.Contains(p.BranchCode))
                            select p;

                    return query.Select(c => Mapper.ToBranchObject(c)).ToList();
                }
            }
            catch (Exception)
            {
                return null;
            }
        }


        public Branch GetBranch(int branchId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                return Mapper.ToBranchObject(db.BranchEntities
                            .SingleOrDefault(p => p.BranchId == branchId));
            }
        }

        public Branch GetBranchByName(string branchName)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                return Mapper.ToBranchObject(db.BranchEntities
                            .SingleOrDefault(p => p.BranchName == branchName));
            }
        }

        public void InsertBranch(Branch branch)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    
                    BranchEntity entity = Mapper.ToBranchEntity(branch);
                    db.BranchEntities.InsertOnSubmit(entity);
                    db.SubmitChanges();

                    branch.BranchId = entity.BranchId;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to create branch record was made before your changes.");
                }
                    
                catch (Exception)
                {
                    throw new Exception("Unable to Insert branch " + branch.BranchName);
                }
            }
        }

        public int UpdateBranch(Branch branch)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    BranchEntity entity = Mapper.ToBranchEntity(branch);
                    db.BranchEntities.Attach(entity, true);
                    db.SubmitChanges();
                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to create branch record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }

        public int DeleteBranch(Branch branch)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    BranchEntity entity = Mapper.ToBranchEntity(branch);
                    db.BranchEntities.Attach(entity, false);
                    db.BranchEntities.DeleteOnSubmit(entity);
                    db.SubmitChanges();

                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to create branch record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }  
     



        public List<Branch> GetBranchesForGroup(int groupId, int pageNo)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<BranchEntity> query = db.BranchEntities;

                query =  from p 
                         in db.BranchEntities
                         where p.GroupId==groupId
                         select p;

                IQueryable<BranchEntity> pageData = query.Skip((pageNo - 1) * pageSize).Take(pageSize).OrderBy(c => c.BranchName);

                return pageData.Select(c => Mapper.ToBranchObject(c)).ToList();
            }
        }


        public List<Branch> GetBranchesForGroup(int groupId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<BranchEntity> query = db.BranchEntities;

                query = from p
                        in db.BranchEntities
                        where p.GroupId == groupId
                        select p;

                return query.Select(c => Mapper.ToBranchObject(c)).ToList();
            }
        }

        public List<Branch> GetBranchesForGroup(string sortExpression,string userName)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<BranchEntity> query = db.BranchEntities;

                query = from branch
                        in db.BranchEntities
                        //join groups in db.GroupEntities on branch.GroupId equals groups.GroupId
                        join unit in db.UnitEntities on branch.BranchId equals unit.BranchId
                        join usersInInfrastructure in db.UsersInStructureEntities on unit.UnitId equals usersInInfrastructure.NodeId 
                        where usersInInfrastructure.UserTypeId==4
                        join users in db.aspnet_Users on usersInInfrastructure.UserId equals users.UserId
                        where users.UserName==userName
                        select branch;

                if (sortExpression.Length > 0)
                {
                    string[] sort = sortExpression.Split(' ');
                    string sortColumn = sort[0];
                    string sortOrder = sort[1];

                    switch (sortColumn)
                    {
                        case "BranchId":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.BranchId);
                            else
                                query = query.OrderByDescending(c => c.BranchId);
                            break;

                        case "BranchName":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.BranchName);
                            else
                                query = query.OrderByDescending(c => c.BranchName);
                            break;
                    }
                }

                return query.Select(c => Mapper.ToBranchObject(c)).ToList();
            }
        }

        public Branch GetBranchForUnit(int unitId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                var query = from branch in db.BranchEntities
                            join unit in db.UnitEntities on branch.BranchId equals unit.BranchId
                            where unit.UnitId == unitId
                            select branch;
                return Mapper.ToBranchObject(query.SingleOrDefault());
            }
        }

        }


    }

